SQL ADO/ODBC

To access SQL databases with ClassiX, the ADO-COM interface can currently be used. The ADO-COM objects use the standardized ODBC database interface. Most large SQL databases provide ODBC drivers for their databases, which have to be installed before the database can be accessed.

Setting up the driver

On the machine on which ClassiX shall run, the ODBC driver (64-bit) matching the database must be installed. First you have to find out which database exactly is to be connected and then you can find the corresponding drivers on the manufacturer's website.

Here are drivers for some known databases:

• MySQL (MariaDB)
• Oracle SQL (Logon required)
• Microsoft SQL
• SQLite

After the driver has been installed, it should be listed in the ODBC (64-bit) data sources among the drivers. To open the management program, simply enter "ODBC" in the Start menu and select the correct version.

Set up DSN

For ODBC, Windows uses the concept of DSNs (Data Source Name) to abstract from the concrete database via an identifier. It is not necessary to set up a DSN, but it makes the connection and exchange of databases much easier. If no DSN is set up, the driver, user, database & password must be specified in the connection string for each connection.

To set up a DSN, go to the User DSN tab in the ODBC Data Source Administrator, click Add and select the installed ODBC driver in the next window.

Afterwards the name is asked for. This name is later specified in the connection string after DSN=, or if the DSN contains all necessary connection information, then it is sufficient to specify this name as a connection string later.

Then, depending on the type of database, the system will ask for the connection data or the file. The user and the database can also be specified directly here, so that this information does not have to be in the connection string. With MySQL the default user is "root" without password.

Via Test DSN a connection to the database can be established here and then one of the databases available there can be selected.

Afterwards a few options can be set.

Now the new DSN should be in the list of user DSNs.

Establish connection

With DSN

With the DSN from the previous step, a connection can be easily established as follows.

Var(connection) 
CreateTransObject(CX_COM_OBJECT) -> connection
"ADODB.Connection" connection Call(CreateFromProgID)
"MySQL-Test" connection Call(Open)

If the information in the DSN is not sufficient, then further specifications can be made in the connection string, if

"DSN=MySQL-Test;UserID=root;DB=test" connection Call(Open)

Without DSN

If no DSN has been defined, the entries made there must be made in the connection string. This is quite error-prone and not recommended due to the driver-specific options. The above example without DSN would look like this:

Var(connection) 
CreateTransObject(CX_COM_OBJECT) -> connection
"ADODB.Connection" connection Call(CreateFromProgID)
"Driver={MariaDB ODBC 3.0 Driver};server=localhost;DB=test;Uid=root" connection Call(Open)

Values that contain spaces must be enclosed in curly brackets.

Connection Strings

The connection strings specified in ADODB.Connection.Open can be either the name of a DSN or a list of key-value pairs. The keys are case sensitive and space sensitive. A list of the standardized keys and their short forms can be found here.

Some drivers define their own keys. For example, the DB key from the above example is needed for MySQL to specify the database, but is not documented in the linked document. Often you have to search the internet for connection string examples for the correct syntax. The https://www.connectionstrings.com/ page also contains a number of sample connection strings that can be used as a starting point.

Execute SQL Query

To execute an SQL command, you can either use an ADODB.command object or, alternatively, execute the query directly on an ADODB.recordset (more simply).

Example - Directly on a recordset

Var(records)
CreateTransObject(CX_COM_OBJECT) -> records
"ADODB.recordset" records Call(CreateFromProgID)

"SELECT * FROM `items`" connection records Call(Open)

// Alternatively with Paging
// "SELECT * FROM `items` LIMIT 10 OFFSET 10" connection records Call(Open)

Example - With Command Object

Var(command, records)
CreateTransObject(CX_COM_OBJECT) -> command
"ADODB.command" command Call(CreateFromProgID)

connection command Call(PutActiveConnection)
"SELECT * FROM `items`" command Call(PutCommandText)

command Call(Execute) -> records

Evaluate result

The results of the query are available in the RecordSet. The RecordSet has the RecordCount property (Call(GetRecordCount)), which contains the number of rows it contains. This can be used, for example, to show the user a progress bar when processing a lot of data.

With GetFields you get a collection of columns of the current row of the recordset. Each column is of type ADODB.field.

A recordset holds a cursor that knows the current line. With MoveNext the cursor can be moved to the next line and with GetEOF you can check if the current line is still valid.

Example - Conversion of a recordset into a vector of JSON objects

Var(numberOfRecords, numberOfColumns, recordVector)
records Call(GetBOF) records Call(GetEOF) & if {
  // Empty recordset
  [ ] # return
}


records Call(MoveFirst)
records Call(GetRecordCount) -> numberOfRecords
records Call(GetFields) Call(GetCount) -> numberOfColumns
// Preallocate vector size if RecordCount is supported
numberOfRecords 0 > if { CreateVector(STACK) } else { [] } -> recordVector
do
  CreateTransObject(CX_JSON_OBJECT) Dup -> jsonObject recordVector Insert
  LocalVar(column, fieldsColl)
  0 -> column
  records Call(GetFields) -> fieldsColl
  do
    LocalVar(itemObject, itemName, itemValue)
    column fieldsColl Call(GetItem) -> itemObject
    itemObject Call(GetName) -> itemName
    itemObject Call(GetValue) -> itemValue
    itemValue jsonObject itemName Put(STACK)
    Incr(column)
    column numberOfColumns <
  while
  DropAll
  records Call(MoveNext)
  records Call(GetEOF) !
while